from openpyxl import load_workbook, Workbook


def get_dict_by(path):
    wb = load_workbook(path)
    print(wb.get_sheet_names)
    sheet = wb.active
    rows = sheet.rows
    list_rows = []
    for row in rows:
        list_rows.append(row)

    dict_sheet = {}
    for row in list_rows[1:]:
        dict_key = row[0].value
        dict_list = []
        for cell in row[1:len(row)]:
            dict_list.append(cell.value)
        dict_sheet[dict_key] = dict_list
    return dict_sheet


def main():
    dict_student_1 = get_dict_by('student1.xlsx')
    dict_student_2 = get_dict_by('student2.xlsx')

    print(dict_student_1)
    print(dict_student_2)

    wb = Workbook()
    # 新建一个工作表，可以指定索引，适当安排其在工作簿中的位置
    wb.create_sheet('Data', index=0)  # 被安排到第二个工作表，index=0就是第一个位置
    sheet = wb.active
    row_0 = ['姓名', '年龄', '婚否', '性别', '专业', '地址']
    sheet.append(row_0)
    for key in dict_student_1:
        row = [key]
        if key in dict_student_2.keys():
            value_list = dict_student_1[key] + dict_student_2[key]
        else:
            value_list = dict_student_1[key] + ['', '']
        for value in value_list:
            row.append(value)
        print(row)
        sheet.append(row)

    for key in dict_student_2:
        row = []
        if key not in dict_student_1.keys():
            row.append(key)
            value_list = ['', '', ''] + dict_student_2[key]
            for value in value_list:
                row.append(value)
            print(row)
            sheet.append(row)

    wb.save(r'student4.xlsx')


if __name__ == '__main__':
    main()
